<?php //strip
/**
 * Database model implementation for odWpEshopPlugin.
 * 
 * @author Ondrej Donek <ondrejd@gmail.com>
 * @category odWpEshopPlugin
 * @version 0.1
 */
class odWpEshopPluginDbModel
{
	
	/**
	 * Creates tables of odWpEshopPlugin.
	 * 
	 * @param string $aVersion
	 * @return void
	 */
	static function create_tables($aVersion = '0.1') 
	{
		global $wpdb;
		
		// Create the database table
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_items` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
					`title` VARCHAR( 255 ) NOT NULL ,
					`image` VARCHAR( 255 ) NOT NULL ,
					`director` VARCHAR( 255 ) NOT NULL ,
					`actor` VARCHAR( 255 ) NOT NULL ,
					`language` VARCHAR( 255 ) NOT NULL ,
					`subtitles` VARCHAR( 255 ) NOT NULL ,
					`custom` VARCHAR( 255 ) NOT NULL ,
					`prize_category_ID` VARCHAR( 255 ) NOT NULL ,
					`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
					`display` TINYINT( 1 ) NOT NULL DEFAULT 1
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
		$wpdb->query("ALTER TABLE `{$wpdb->prefix}eshop_items` 
						ADD `shopno` INT( 10 ) NOT NULL AFTER `ID` ;");
		$wpdb->query("ALTER TABLE `{$wpdb->prefix}eshop_items` 
						ADD `title_orig` VARCHAR( 255 ) NOT NULL AFTER `title` ;");
		$wpdb->query("ALTER TABLE `{$wpdb->prefix}eshop_orders` 
						ADD `finished` TINYINT( 1 ) NOT NULL AFTER `created` ,
						ADD `finished_when` TIMESTAMP NULL AFTER `finished` ");
		//$wpdb->query("ALTER TABLE `{$wpdb->prefix}eshop_items` 
		//				ADD `not_available` INT( 1 ) NOT NULL  
		//				COMMENT 'Indicates that eshop item is not available on the stock.' ;");
		
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_genre_categories` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
					`parent_category_ID` INT( 11 ) UNSIGNED NOT NULL ,
					`title` VARCHAR( 255 ) NOT NULL ,
					`description` VARCHAR( 255 ) NOT NULL ,
					`display` TINYINT( 1 ) NOT NULL DEFAULT 1
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
		
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_items_to_genre_categories` (
					`item_ID` INT( 11 ) UNSIGNED NOT NULL ,
					`genre_category_ID` INT( 11 ) UNSIGNED NOT NULL 
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
		
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_prize_categories` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
					`title` VARCHAR( 255 ) NOT NULL ,
					`description` VARCHAR( 255 ) NOT NULL ,
					`prize` INTEGER( 4 ) NOT NULL ,
					`display` TINYINT( 1 ) NOT NULL DEFAULT 1
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
		
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_users` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
					`wp_user_ID` BIGINT( 20 ) NOT NULL ,
					`firstname` VARCHAR( 255 ) NOT NULL , 
					`surname` VARCHAR( 255 ) NOT NULL ,
					`street` VARCHAR( 255 ) NOT NULL , 
					`town` VARCHAR( 255 ) NOT NULL ,
					`zip` VARCHAR( 255 ) NOT NULL ,
					`phone` VARCHAR( 255 ) NOT NULL ,
					`permition` INT( 1 ) NOT NULL DEFAULT 0 
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
		
		$wpdb->query("
				CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}eshop_orders` (
					`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
					`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
					`finished` tinyint(1) NOT NULL,
					`finished_when` timestamp NULL DEFAULT NULL,
					`user_ID` int(11) NOT NULL,
					`delivery` enum('personal','postoffice') NOT NULL DEFAULT 'personal',
					`status_ID` int(11) NOT NULL,
					PRIMARY KEY (`ID`)
				) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE utf8_general_ci AUTO_INCREMENT=1 ;");
		
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_orders_items` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
					`order_ID` INT( 11 ) UNSIGNED NOT NULL , 
					`eshop_item_ID` INT( 11 ) UNSIGNED NOT NULL ,
					`count` INT( 3 ) NOT NULL DEFAULT 1 , 
					`total_prize` FLOAT NOT NULL 
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
		
		$wpdb->query("
				CREATE TABLE `{$wpdb->prefix}eshop_orders_statuses` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
					`label` VARCHAR( 255 ) NOT NULL 
				) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
	    
	    $wpdb->query("
	            CREATE TABLE `{$wpdb->prefix}eshop_faq` (
					`ID` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
                    `sender_name` VARCHAR( 255 ) NOT NULL ,
                    `sender_mail` VARCHAR( 255 ) NOT NULL ,
                    `sender_phone` VARCHAR( 10 ) NOT NULL ,
                    `question` LONGTEXT NOT NULL
	            ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; ");
	}
	
	/**
	 * Returns total count of available items in our eshop.
	 * 
	 * @param boolean $aAlsoDeleted
	 * @param string $aSearch
	 * @return integer
	 */
	static function getEshopItemsCount($aAlsoDeleted = false, $aSearch = '') 
	{
		global $wpdb;
		
		$count = $wpdb->get_results("
				SELECT 
					count(`ID`) 
				FROM `{$wpdb->prefix}eshop_items` 
				WHERE " . ((!$aAlsoDeleted) ? '`display` = 1' : '1') . " " . 
				(($aSearch != '') ? " AND (`shopno` = '{$aSearch}' OR `title` LIKE '%{$aSearch}%' OR `director` LIKE '%{$aSearch}%' OR `actor` LIKE '%{$aSearch}%' ) " : ""), ARRAY_N);
		
		if(!is_array($count)) {
			return 0;
		}
		
		return (int) $count[0][0];
	}
	
	/**
	 * Get eshop items as array.
	 *
	 * @param integer $aCount
	 * @param boolean $aAlsoDeleted (optional)
	 * @param odWpEshopPluginPager $aPager
	 * @param boolean $aAlsoDeleted
	 * @param string $aSearch
	 * @return array
     */
	static function getEshopItems($aCount = 6, $aPager = null, $aAlsoDeleted = false, $aSearch = '')
	{
		global $wpdb;
		
		$ret = array();
		$limit_from = (is_null($aPager)) ? 0 : $aPager->mLimitItemsFrom;
		$limit_to = $aCount;
		$query = "
				SELECT 
					`t2`.`ID` AS `item_ID`, 
					`t2`.`shopno` AS `item_shopno`, 
					`t2`.`title` AS `item_title`,
					`t2`.`title_orig` AS `item_title_orig`,
					`t2`.`image` AS `item_image`,
					`t4`.`title` AS `prize_category`,
					`t4`.`prize` AS `prize_value`,
					`t2`.`director` AS `item_director`,
					`t2`.`actor` AS `item_actor`,
					`t2`.`language` AS `item_language`,
					`t2`.`subtitles` AS `item_subtitles`,
					`t2`.`custom` AS `item_custom`,
					`t2`.`display` AS `item_display`  
				FROM `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`  
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID` 
				WHERE 
					 " . ((!$aAlsoDeleted) ? '`t2`.`display` = 1' : '1') . "  " . 
    				(($aSearch != '') ? " AND (`t2`.`shopno` = '{$aSearch}' OR `t2`.`title` LIKE '%{$aSearch}%' OR `t2`.`director` LIKE '%{$aSearch}%' OR `t2`.`actor` LIKE '%{$aSearch}%' ) " : "") . " 
				GROUP BY `item_title` 
				ORDER BY `t2`.`created` ASC   
				LIMIT {$limit_from}, {$limit_to} ";
		/*echo "<!-- 
{$query} 
-->";*/
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_array($rows)) {
			foreach($rows as $row) {
				$ret[] = self::_process_dbitem($row);
			}
		}
		
		return $ret;
	}
	
	// Pouzito pro `odEshopNewestItemsWidget`
	static function getLatestEshopItems($aCount = 6, $aCategory = 0)
	{
		global $wpdb;
		
		$ret = array();
		$query = "
				SELECT 
					`t2`.`ID` AS `item_ID`, 
					`t2`.`shopno` AS `item_shopno`, 
					`t2`.`title` AS `item_title`,
					`t2`.`title_orig` AS `item_title_orig`,
					`t2`.`image` AS `item_image`,
					`t4`.`title` AS `prize_category`,
					`t4`.`prize` AS `prize_value`,
					`t2`.`director` AS `item_director`,
					`t2`.`actor` AS `item_actor`,
					`t2`.`language` AS `item_language`,
					`t2`.`subtitles` AS `item_subtitles`,
					`t2`.`custom` AS `item_custom`,
					`t2`.`display` AS `item_display`  
				FROM `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`  
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID` 
				WHERE 1 " . (($aCategory != 0) ? "AND `t1`.`genre_category_ID`='{$aCategory}' " : "") . " 
				GROUP BY `item_title` 
				ORDER BY `t2`.`created` DESC 
				LIMIT 0, {$aCount} ";
		/*echo "<!--
{$query}
-->";*/
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_array($rows)) {
			foreach($rows as $row) {
				$ret[] = self::_process_dbitem($row);
			}
		}
		
		return $ret;
	}
	
	/**
	 * Returns all eshop items that belongs to given genre category.
	 *
	 * @param integer $aCategoryId
	 * @param odWpEshopPluginPager $aPager
	 * @return array|null
	 */
	static function getEshopItemsByCategory($aCategoryId = null, $aPager = null, $aAlsoDeleted = false)
	{
		if(is_null($aCategoryId)) {
			return null;
		}
		
		global $wpdb;
		
		$ret = array();
		$limit_from = (is_null($aPager)) ? 0 : $aPager->mLimitItemsFrom;
		$limit_to = OD_ESHOP_PAGER_ITEMS_LIMIT;
		$query = "
				SELECT 
					`t2`.`ID` AS `item_ID`, 
					`t2`.`shopno` AS `item_shopno`, 
					`t2`.`title` AS `item_title`,
					`t2`.`title_orig` AS `item_title_orig`,
					`t2`.`image` AS `item_image`,
					`t4`.`title` AS `prize_category`,
					`t4`.`prize` AS `prize_value`, 
					`t2`.`director` AS `item_director`,
					`t2`.`actor` AS `item_actor`,
					`t2`.`language` AS `item_language`,
					`t2`.`subtitles` AS `item_subtitles`,
					`t2`.`custom` AS `item_custom`,
					`t2`.`display` AS `item_display`  
				FROM `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`  
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID` 
				WHERE  
					 " . ((!$aAlsoDeleted) ? '`t2`.`display` = 1' : '1') . " AND 
					`t1`.`genre_category_ID` = '{$aCategoryId}' 
				GROUP BY `item_title` 
				ORDER BY `t2`.`created`, `item_title` ASC  
				LIMIT {$limit_from}, {$limit_to} ";
		/*echo "<!-- 

{$query} 

-->";*/
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_array($rows)) {
			foreach($rows as $row) {
				$ret[] = self::_process_dbitem($row);
			}
		}
		
		return $ret;
	}
	
	/**
	 * Get count of eshop items for the given category
	 * 
	 * @param integer $aCategoryId
	 * @return integer
	 */
	static function getEshopItemsByCategoryCount($aCategoryId = null, $aAlsoDeleted = false)
	{
		if(is_null($aCategoryId)) {
			return 0;
		}
		
		global $wpdb;
		
		$query = "
				SELECT 
					count( `t2`.`ID` ) AS `items_count`   
				FROM 
					`{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
				LEFT JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`      
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID`
				WHERE  
					 " . ((!$aAlsoDeleted) ? '`t2`.`display` = 1' : '1') . " AND 
					`t1`.`genre_category_ID` = '{$aCategoryId}' ";
		
		$col = $wpdb->get_results($query, ARRAY_A);
		
		if(!is_array($col)) {
			return 0;
		}
		
		return (@count($col) == 1) ? (int) $col[0]['items_count'] : 0;
	}
	
	/**
	 * Returns all eshop items that starts with given char.
	 *
	 * @param integer $aCategoryId
	 * @param odWpEshopPluginPager $aPager
	 * @return array
	 */
	static function getEshopItemsByFirstChar($aSelChar = 'a', $aPager = null)
	{
		global $wpdb;
		
		$ret = array();
		$limit_from = (is_null($aPager)) ? 0 : $aPager->mLimitItemsFrom;
		$limit_to = /*(is_null($aPager)) ? */OD_ESHOP_PAGER_ITEMS_LIMIT/*: $aPager->mLimitItemsTo*/;
		$query = "
				SELECT 
					`t1`.`ID` AS `item_ID`, 
					`t1`.`shopno` AS `item_shopno`, 
					`t1`.`title` AS `item_title`,
					`t1`.`title_orig` AS `item_title_orig`,
					`t1`.`image` AS `item_image`,
					`t2`.`title` AS `prize_category`,
					`t2`.`prize` AS `prize_value`, 
					`t1`.`director` AS `item_director`,
					`t1`.`actor` AS `item_actor`,
					`t1`.`language` AS `item_language`,
					`t1`.`subtitles` AS `item_subtitles`,
					`t1`.`custom` AS `item_custom`,
					`t1`.`display` AS `item_display` 
				FROM 
						`{$wpdb->prefix}eshop_items` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t2` ON `t2`.`ID` = `t1`.`prize_category_ID` 
				WHERE 
					`t1`.`display` = 1 AND 
					`t1`.`title` REGEXP '^{$aSelChar}'  
				ORDER BY `item_title` 
				LIMIT {$limit_from}, {$limit_to} ";
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_array($rows)) {
			foreach($rows as $row) {
				$ret[] = self::_process_dbitem($row);
			}
		}
		
		return $ret;
	}
	
	static function getEshopItemsByFirstCharCount($aSelChar)
	{
		global $wpdb;
		
		$query = "
				SELECT 
					count( `t1`.`ID` ) AS `items_count` 
				FROM `{$wpdb->prefix}eshop_items` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t2` ON `t2`.`ID` = `t1`.`prize_category_ID` 
				WHERE
					`t1`.`display` = 1 AND 
					`t1`.`title` REGEXP '^{$aSelChar}' ";
		
    $col = $wpdb->get_results($query, ARRAY_A);
		
		if(!is_array($col)) {
			return 0;
		}
		
		return (@count($col) == 1) ? (int) $col[0]['items_count'] : 0;	
	}
	
	/**
	 * Returns eshop item by its ID.
	 *
	 * @param integer $aItemId
	 * @return array
	 */
	static function getEshopItemById($aItemId)
	{
		global $wpdb;
		
		$ret = $wpdb->get_row("
				SELECT 
					`t2`.`ID` AS `item_ID`, 
					`t2`.`shopno` AS `item_shopno`, 
					`t2`.`title` AS `item_title`,
					`t2`.`title_orig` AS `item_title_orig`,
					`t2`.`image` AS `item_image`,
					`t4`.`title` AS `prize_category`,
					`t4`.`prize` AS `prize_value`,
					`t2`.`director` AS `item_director`,
					`t2`.`actor` AS `item_actor`,
					`t2`.`language` AS `item_language`,
					`t2`.`subtitles` AS `item_subtitles`,
					`t2`.`custom` AS `item_custom`,
					`t2`.`display` AS `item_display`  
				FROM `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`  
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID` 
				WHERE
					`t1`.`item_ID` = '{$aItemId}' 
				GROUP BY `item_title` 
				ORDER BY `t2`.`created`, `item_title` ASC
				LIMIT 1 ", ARRAY_A);
		
		return self::_process_dbitem($ret);
	}
	
	/**
	 * Process single database record that represents an eshop item. 
	 * 
	 * @param array $aItem
	 * @return array
	 */
	/*protected */static function _process_dbitem($aItem)
	{
		if(!is_array($aItem)) {
			return null;
		}
		
		global $wpdb, $od_eshop_wpplugin;
		$options = $od_eshop_wpplugin->get_options();
		
		$image = (empty($aItem['item_image'])) ? $options['noimage_name'] : $aItem['item_image'];
		
		$aItem['image_url1'] = $od_eshop_wpplugin->get_image_url(str_replace('.jpg', OD_ESHOP_THUMB_SUFFIX_ORIGINAL, $image));
		$aItem['image_url2'] = $od_eshop_wpplugin->get_image_url(str_replace('.jpg', OD_ESHOP_THUMB_SUFFIX_NORMAL, $image));
		$aItem['image_url3'] = $od_eshop_wpplugin->get_image_url(str_replace('.jpg', OD_ESHOP_THUMB_SUFFIX_SMALL, $image));
		$aItem['detail_url'] = $od_eshop_wpplugin->get_detail_page_url($aItem['item_ID']);
		
		$aItem['genre_categories'] = '';
		$query = "
		        SELECT 
				    GROUP_CONCAT( `t2`.`title` ORDER BY `t2`.`title` ASC SEPARATOR ', ' ) AS `genre_categories` 
				FROM `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1`
				JOIN `{$wpdb->prefix}eshop_genre_categories` AS `t2` ON `t2`.`ID` = `t1`.`genre_category_ID` 
				WHERE `t1`.`item_ID` = '" . $aItem['item_ID'] . "' ";
		$col = $wpdb->get_results($query, ARRAY_A);
		if(is_array($col)) {
			$aItem['genre_categories'] = (@count($col) == 1) ?  $col[0]['genre_categories'] : '';
		}
		
		return $aItem;
	}
	
	// ========================================================================
	// Prize categories
	
	/**
	 * Returns all prize categories
	 * 
	 * @param boolean $aOnlyUsedCategories (optional)
	 * @return array
	 */
	static function getPrizeCategories($aOnlyUsedCategories = false)
	{
	    // TODO Finish Get 'only used prize categories'.
	    
	    global $wpdb, $od_eshop_wpplugin;
	    
	    $ret = array();
	    $query = "
	            SELECT `ID`, `title`, `prize` 
	            FROM `{$wpdb->prefix}eshop_prize_categories` 
	            WHERE `title` != '' 
	            ORDER BY `title` ASC ";
	    $rows = $wpdb->get_results($query, ARRAY_A);
	    
	    if(is_null($rows)) {
	        return $ret;
	    }
	    
	    foreach($rows as $row) {
	        $row['public_url'] = $od_eshop_wpplugin->get_main_page_url() . '&amp;prize_cat_id=' . $row['ID'];
	        $ret[] = $row;
	    }
	    
	    return $ret;
	}
	
	/**
	 * Returns prize category by its ID.
	 *
	 * @param integer $aCategoryId
	 * @return array|NULL
	 */// TODO get_prize_category
	static function getPrizeCategory($aCategoryId = null)
	{
	    if(is_null($aCategoryId)) {
	        return null;
	    }
	    
		global $wpdb, $od_eshop_wpplugin;
		
		$cat_id = (int) $aCategoryId;
		$query = "
	            SELECT `ID`, `title`, `prize` 
	            FROM `{$wpdb->prefix}eshop_prize_categories` 
	            WHERE `ID` = '{$cat_id}' 
	            LIMIT 1 ";
		$category = $wpdb->get_row($query, ARRAY_A);
		$category['public_url'] = $od_eshop_wpplugin->get_main_page_url() . '&amp;prize_cat_id=' . $category['ID'];
		
		return $category;
	}
	
	/**
	 * Inserts new prize category into the database.
	 * 
	 * @param string $aTitle
	 * @param string $aPrize
	 * @retun boolean
	 */
	static function addPrizeCategory($aTitle = '', $aPrize = '')
	{
	    global $wpdb;
	    
	    $query = "
			    INSERT INTO `{$wpdb->prefix}eshop_prize_categories`
				    ( `ID` , `title` , `prize` )
				VALUES
				    ( NULL , '{$aTitle}', '{$aPrize}' ) ";
	    $res = $wpdb->query($query);
	    
	    return ($res) ? true : false;
	}
	
	/**
	 * Updates prize category with given ID and values.
	 * 
	 * @param string $aTitle
	 * @param string $aPrize
	 * @retun boolean
	 */
	static function updatePrizeCategory($aCategoryId = null, $aTitle = '', $aPrize = '')
	{
	    if(is_null($aCategoryId)) {
	        return false;
	    }
	    
	    global $wpdb;
	    
	    $cat_id = (int) $aCategoryId;
	    $query = "
                UPDATE `{$wpdb->prefix}eshop_prize_categories`
                SET
                    `title` = '{$aTitle}',
                    `prize` = '{$aPrize}'
                WHERE `ID` = '{$cat_id}' 
                LIMIT 1 ";
	    $res = $wpdb->query($query);
	    
	    return ($res) ? true : false;
	}
	
	/**
	 * Deletes prize category with given ID from the database.
	 * 
	 * @param integer $aCategoryId
	 * @return boolean
	 */
	static function deletePrizeCategory($aCategoryId = null)
	{
	    if(is_null($aCategoryId)) {
	        return false;
	    }
	    
	    global $wpdb;
	    
	    $cat_id = (int) $aCategoryId;
		$query = "
		        DELETE FROM `{$wpdb->prefix}eshop_prize_categories` 
		        WHERE `ID` = '{$cat_id}' 
		        LIMIT 1 ;";
		$res = $wpdb->query($query);
		
		return ($res) ? true : false;
	}
	
	// ========================================================================
	// Genre categories
	
	/**
	 * Returns array of all genres.
	 * 
	 * @param boolean $aOnlyRootCategories (optional)
	 * @param boolean $aOnlyUsedCategories (optional)
	 * @return array
	 */
	static function getGenreCategories($aOnlyRootCategories = false, $aOnlyUsedCategoreis = false)
	{
    // TODO Finish 'only used genre categories' mode!
    
    global $wpdb, $od_eshop_wpplugin;
    
    $categories = array();
		$query = "SELECT * FROM `{$wpdb->prefix}eshop_genre_categories` WHERE ";
		$query .= ($aOnlyRootCategories === true) ? '`parent_category_ID` = 0 ' : '1 ';
		$rows = $wpdb->get_results($query, ARRAY_A);
		
    if(is_null($rows)) {
        return $categories;
    }
    
    foreach($rows as $row) {
        $row['public_url'] = $od_eshop_wpplugin->get_main_page_url() . '&amp;cat_id=' . $row['ID'];
        $categories[] = $row;
    }
	    
		return $categories;
	}
	
	/**
	 * Returns genre category by its ID.
	 *
	 * @param integer $aCategoryId
	 * @return array
	 */
	static function getGenreCategory($aCategoryId)
	{
		global $wpdb;
		
		$query = "SELECT * FROM `{$wpdb->prefix}eshop_genre_categories` WHERE `ID` = '{$aCategoryId}' LIMIT 1 ";
		$category = $wpdb->get_row($query, ARRAY_A);
		
		return $category;
	}
	
	// ========================================================================
	// Eshop users
	
	/**
	 * Check if given login is already used.
	 * 
	 * @param string $aLogin
	 * @return boolean
	 */
	static function checkIfLoginExists($aLogin = '') 
	{
		if(is_null($aLogin) || empty($aLogin)) {
			return false;
		}
		
		global $wpdb;
		
		$query = "SELECT count( `ID` ) FROM `{$wpdb->prefix}users` WHERE `user_login` = '{$aLogin}' ";
		$res = $wpdb->get_col($query);
		
		if(is_array($res)) {
			return ((int) $res[0] == 1) ? true : false;
		}
		
		return false;
	}
	
	/**
	 * Returns array with eshop users
	 *
	 * @return array
	 */
	static function getEshopUsers()
	{
		global $wpdb;
		
		$ret = array();
		$query = "
				SELECT 
					`t1`.`ID` AS `ID`, 
					`t1`.`wp_user_ID` AS `wp_user_id`, 
					`t1`.`street` AS `street`, 
					`t1`.`town` AS `town`, 
					`t1`.`zip` AS `zip`, 
					`t1`.`phone` AS `phone`, 
					`t1`.`permition` AS `permition` 
				FROM `{$wpdb->prefix}eshop_users` AS `t1` 
				WHERE 1  
				GROUP BY `ID` ";
		$users = $wpdb->get_results($query, ARRAY_A);
		
		if(is_null($users)) {
			return $ret;
		}
		
		foreach($users as $user) {
			$user['wp_user'] = new WP_User($user['wp_user_id']);
			$ret[] = $user;
		}
		
		return $ret;
	}
	
	// ========================================================================
	// Eshop orders
	
	/**
	 * Adds new order into the database.
	 * 
	 * @param integer $aOrderId
	 * @param integer $aItemId
	 * @param integer $aCount
	 * @return boolean
	 */
	static function addOrderItem($aOrderId = null, $aItemId = null, $aCount = 1)
	{
		if(is_null($aOrderId) || is_null($aItemId)) {
			return false;
		}
		
		global $wpdb;
		
		$query = "
			INSERT INTO `{$wpdb->prefix}eshop_orders_items` 
				( `order_ID` , `eshop_item_ID`, `count` ) 
			VALUES 
				( '{$aOrderId}' , '{$aItemId}' , '{$aCount}' ) ;";
		
		return $wpdb->query($query);
	}
	
	/**
	 * Returns orders.
	 * 
	 * @param odWpEshopPluginPager $aPager
	 * @return array
	 */
	static function getOrders($aPager = null)
	{
		global $wpdb;
		
		$query = "
			SELECT 
				`t1`.`ID` AS `order_ID` , 
				`t1`.`created` AS `order_created` ,
				`t1`.`finished` AS `order_finished` ,
				`t1`.`finished_when` AS `order_finished_when` ,
				`t1`.`delivery` AS `order_delivery` , 
				`t1`.`user_ID` AS `order_user_ID` , 
				`t2`.`street` AS `order_user_street` , 
				`t2`.`town` AS `order_user_town` , 
				`t2`.`zip` AS `order_user_zip` , 
				`t2`.`phone` AS `order_user_phone` , 
				`t2`.`permition` AS `order_user_permition` , 
				`t2`.`wp_user_ID` AS `order_wpuser_ID` , 
				`t1`.`status_ID` AS `order_status_ID` , 
				`t3`.`label` AS `order_status_label` , 
				(SELECT count( `t4`.`ID` ) FROM `{$wpdb->prefix}eshop_orders_items` AS `t4` WHERE `t4`.`order_ID` = `t1`.`ID`) AS `order_items_count` , 
				(SELECT SUM( `t7`.`prize` ) FROM `{$wpdb->prefix}eshop_orders_items` AS `t5` JOIN `{$wpdb->prefix}eshop_items` AS `t6` ON `t6`.`ID` = `t5`.`eshop_item_ID` JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t7` ON `t7`.`ID` = `t6`.`prize_category_ID` WHERE `t5`.`order_ID` = `t1`.`ID` ) AS `order_total_prize` 
			FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
			LEFT JOIN `{$wpdb->prefix}eshop_users` AS `t2` ON `t2`.`wp_user_ID` = `t1`.`user_ID` 
			LEFT JOIN `{$wpdb->prefix}eshop_orders_statuses` AS `t3` ON `t3`.`ID` = `t1`.`status_ID`   
			WHERE 
			    `t1`.`finished` = 1 
			ORDER BY `order_created` ASC ";
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_null($rows)) {
			return array();
		}
		
		$ret = array();
		foreach($rows as $row) {
			$row['order_wpuser'] = new WP_User($row['order_wpuser_ID']);
			$row['order_total_prize'] = (int) $row['order_total_prize'];
			$row['order_delivery_prize'] = ($row['order_delivery'] == 'postoffice') ? 95 : 0; 
			$row['order_total_prize_with_delivery'] = $row['order_total_prize'] + $row['order_delivery_prize'];
			$ret[] = $row;
		}
		
		return $ret;
	}
	
	/**
	 * Returns order by its ID.
	 * 
	 * The returned object has these properties: 
	 *      `order_ID` (string|int)
	 *      `order_created` (string|timestamp)  
	 *      `order_finished` (string|smallint:[1,0])
	 *      `order_finished_when` (string|timestamp)
	 *      `order_delivery` (string)
	 *      `order_user_ID` (string|int)
	 *      `order_user_street` (string)
	 *      `order_user_town` (string)
	 *      `order_user_zip` (string)
	 *      `order_user_phone` (string)
	 *      `order_user_permition` (string|smallint:[1,0])
	 *      `order_wpuser_ID` (string|int)
	 *      `order_status_ID` (string|int)
	 *      `order_status_label` (string)
	 *      `order_items_count` (integer)
	 *      `order_items` (array)
	 * 
	 * @param integer $aOrderId
	 * @return object|null
	 */
	static function getOrderById($aOrderId = null)
	{
		if(is_null($aOrderId)) {
			return null;
		}
		
		global $wpdb;
		
		$query = "
			SELECT 
				`t1`.`ID` AS `order_ID` , 
				`t1`.`created` AS `order_created` ,
				`t1`.`finished` AS `order_finished` ,
				`t1`.`finished_when` AS `order_finished_when` ,
				`t1`.`delivery` AS `order_delivery` , 
				`t1`.`user_ID` AS `order_user_ID` , 
				`t2`.`street` AS `order_user_street` , 
				`t2`.`town` AS `order_user_town` , 
				`t2`.`zip` AS `order_user_zip` , 
				`t2`.`phone` AS `order_user_phone` , 
				`t2`.`permition` AS `order_user_permition` , 
				`t2`.`wp_user_ID` AS `order_wpuser_ID` , 
				`t1`.`status_ID` AS `order_status_ID` , 
				`t3`.`label` AS `order_status_label` , 
				(SELECT count(`t4`.`ID`) FROM `{$wpdb->prefix}eshop_orders_items` AS `t4` WHERE `t4`.`order_ID` = `t1`.`ID`) AS `order_items_count`
			FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
			LEFT JOIN `{$wpdb->prefix}eshop_users` AS `t2` ON `t2`.`wp_user_ID` = `t1`.`user_ID` 
			LEFT JOIN `{$wpdb->prefix}eshop_orders_statuses` AS `t3` ON `t3`.`ID` = `t1`.`status_ID`   
			WHERE 
				`t1`.`ID` = '{$aOrderId}' 
			LIMIT 1 ";
		
		$res = $wpdb->get_row($query);
		$res->order_wpuser = new WP_User($res->order_wpuser_ID);
		
		$res->order_items = array();
		if((int) $res->order_items_count > 0) {
			$res->order_items = self::getOrderItems($aOrderId);
		}
		
		$query2 = "
			SELECT 
				SUM( `t3`.`prize` )
			FROM `{$wpdb->prefix}eshop_orders_items` AS `t1`
			JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`eshop_item_ID`
			JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t3` ON `t3`.`ID` = `t2`.`prize_category_ID`
			WHERE `t1`.`order_ID` = '{$aOrderId}' ";
		$res2 = $wpdb->get_col($query2);
		
		$res->order_total_prize = (is_null($res2)) ? 0 : (int) $res2[0];
		$res->order_delivery_prize = ($res->order_delivery == 'postoffice') ? 95 : 0; 
		$res->order_total_prize_with_delivery = $res->order_total_prize + $res->order_delivery_prize;
		
		return $res;
	}
	
	/**
	 * Returns orders.
	 *
	 * @param integer $aUserId
	 * @param odWpEshopPluginPager $aPager
	 * @return array
	 */
	static function getOrdersByUserId($aUserId = null, $aPager = null)
	{
		if(is_null($aUserId)) {
			return array();
		}
		
		global $wpdb;
		
		$query = "
			SELECT 
				`t1`.`ID` AS `order_ID` , 
				`t1`.`created` AS `order_created` ,
				`t1`.`finished` AS `order_finished` ,
				`t1`.`finished_when` AS `order_finished_when` ,
				`t1`.`delivery` AS `order_delivery` , 
				`t1`.`user_ID` AS `order_user_ID` , 
				`t2`.`street` AS `order_user_street` , 
				`t2`.`town` AS `order_user_town` , 
				`t2`.`zip` AS `order_user_zip` , 
				`t2`.`phone` AS `order_user_phone` , 
				`t2`.`permition` AS `order_user_permition` , 
				`t2`.`wp_user_ID` AS `order_wpuser_ID` , 
				`t1`.`status_ID` AS `order_status_ID` , 
				`t3`.`label` AS `order_status_label` , 
				(SELECT count( `t4`.`ID` ) FROM `{$wpdb->prefix}eshop_orders_items` AS `t4` WHERE `t4`.`order_ID` = `t1`.`ID`) AS `order_items_count` , 
				(SELECT SUM( `t7`.`prize` ) FROM `{$wpdb->prefix}eshop_orders_items` AS `t5` JOIN `{$wpdb->prefix}eshop_items` AS `t6` ON `t6`.`ID` = `t5`.`eshop_item_ID` JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t7` ON `t7`.`ID` = `t6`.`prize_category_ID` WHERE `t5`.`order_ID` = `t1`.`ID` ) AS `order_total_prize` 
			FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
			LEFT JOIN `{$wpdb->prefix}eshop_users` AS `t2` ON `t2`.`wp_user_ID` = `t1`.`user_ID` 
			LEFT JOIN `{$wpdb->prefix}eshop_orders_statuses` AS `t3` ON `t3`.`ID` = `t1`.`status_ID`   
			WHERE 
			    `t1`.`finished` = 1 AND
					`t1`.`user_id` = '{$aUserId}' 
			ORDER BY `order_created` ASC ";
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_null($rows)) {
			return array();
		}
		
		$ret = array();
		foreach($rows as $row) {
			$row['order_wpuser'] = new WP_User($row['order_wpuser_ID']);
			$row['order_total_prize'] = (int) $row['order_total_prize'];
			$row['order_delivery_prize'] = ($row['order_delivery'] == 'postoffice') ? 95 : 0; 
			$row['order_total_prize_with_delivery'] = $row['order_total_prize'] + $row['order_delivery_prize'];
			$ret[] = $row;
		}
		
		return $ret;
	}
	
	/**
	 * Updates order.
	 * 
	 * @param integer $aOrderId
	 * @param array $aValues
	 * @return boolean
	 */
	static function updateOrder($aOrderId = null, $aValues = null) 
	{
		if(is_null($aOrderId) || is_null($aValues)) {
			return false;
		}
		
		global $wpdb;
		
		$query = "UPDATE `{$wpdb->prefix}eshop_orders` SET ";
		$i = 0;
		
		foreach($aValues as $aKey => $aValue) {
			switch($aKey) {
				case 'created':
				case 'finished':
				case 'finished_when':
				case 'user_ID':
				case 'delivery':
				case 'status_ID': 
					if($aKey == 'finished_when' && is_null($aValue)) {
						$query .= " `finished_when` = CURRENT_TIMESTAMP ";
					} else {
						$query .= " `{$aKey}` = '{$aValue}' ";
					} 
					break;
			}
			
			if(++$i < count($aValues)) $query .= ", ";
		}
		 
		$query .= "WHERE `ID` = '{$aOrderId}' LIMIT 1 ";
		
		return $wpdb->query($query);
	}
	
	/**
	 * Returns current (un-finished) order for the current user. If no such 
	 * order is existing will create new one. The returned object has same 
	 * structure as it if was returned by odWpEshopPluginDbModel::getOrderById 
	 * method.
	 * 
	 * @param integer $aWpUserId
	 * @return object|NULL
	 */
	static function getCurrentOrderForUser($aWpUserId = null) 
	{
		if(is_null($aWpUserId)) {
			return null;
		}
		
		global $wpdb;
		
		$res = $wpdb->get_col("SELECT `ID` FROM `{$wpdb->prefix}eshop_orders` WHERE `user_ID` = '{$aWpUserId}' AND `finished` = 0 LIMIT 1 ");
		
		if(count($res) == 0) {
			// Order currently doesn't exist so create new one
			$res = $wpdb->query("INSERT INTO `{$wpdb->prefix}eshop_orders` ( `finished`, `user_ID` ) VALUES ( 0, '{$aWpUserId}' ) ");
			
			return self::getOrderById($wpdb->insert_id);
		} else {
			return self::getOrderById($res[0]);
		}		
	}
	
	/**
	 * Returns items for order with given ID.
	 * 
	 * @param integer $aOrderId
	 * @return array|NULL
	 */
	static function getOrderItems($aOrderId = null)
	{
		if(is_null($aOrderId)) {
			return null;
		}
		
		global $wpdb;
		
		$query = "
			SELECT 
				`t1`.`ID` AS `order_item_ID` , 
				`t1`.`eshop_item_ID` AS `eshop_item_ID` ,
				`t2`.`shopno` AS `eshop_item_shopno` , 
				`t2`.`title` AS `eshop_item_title` ,  
				`t2`.`title_orig` AS `eshop_item_title_orig` ,  
				`t2`.`image` AS `eshop_item_image` ,  
				`t2`.`director` AS `eshop_item_director` ,  
				`t2`.`actor` AS `eshop_item_actor` ,  
				`t2`.`language` AS `eshop_item_language` ,  
				`t2`.`subtitles` AS `eshop_item_subtitles` , 
				`t2`.`custom` AS `eshop_item_custom` , 
				`t2`.`prize_category_ID` AS `eshop_item_prize_category_ID` , 
				`t3`.`title` AS `eshop_item_prize_category_title` , 
				`t3`.`prize` AS `eshop_item_prize_category_prize` ,
				`t1`.`count` AS `order_item_count` 
			FROM `{$wpdb->prefix}eshop_orders_items` AS `t1` 
			JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`eshop_item_ID` 
			JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t3` ON `t3`.`ID` = `t2`.`prize_category_ID` 
			WHERE 
				`t1`.`order_ID` = '{$aOrderId}' AND 
				`t2`.`display` = 1 ";
		$res = $wpdb->get_results($query);
		
		return $res;
	}
	
	/**
	 * Removes order item with given ID.
	 * 
	 * @param integer $aOrderItemId
	 * @return boolean
	 */
	static function removeOrderItem($aOrderItemId = null)
	{
		if(is_null($aOrderItemId)) {
			return false;
		}
		
		global $wpdb;
		
		$query = "DELETE FROM `{$wpdb->prefix}eshop_orders_items` WHERE `ID` = '{$aOrderItemId}' LIMIT 1 ";
		
		return $wpdb->query($query);
	}
	
	
	// =========================================================================
	// SEARCH
	
	/*private */static function _get_query_for_eshopitems_search($aCount, $aTitle, $aTitleOrig, $aDirector, $aActor, $aShopno, $aCategory, $aShowHidden = false)
	{
		global $wpdb;
		
		$query = "SELECT ";

		if(!$aCount) {
			$query .= "`t2`.`ID` AS `item_ID`, 
					`t2`.`shopno` AS `item_shopno`, 
					`t2`.`title` AS `item_title`,
					`t2`.`title_orig` AS `item_title_orig`,
					`t2`.`image` AS `item_image`,
					`t4`.`title` AS `prize_category`,
					`t4`.`prize` AS `prize_value`,
					`t2`.`director` AS `item_director`,
					`t2`.`actor` AS `item_actor`,
					`t2`.`language` AS `item_language`,
					`t2`.`subtitles` AS `item_subtitles`,
					`t2`.`custom` AS `item_custom`,
					`t2`.`display` AS `item_display` ";
		} else {
			$query .= "COUNT( `t2`.`ID` ) AS `items_count` ";
		}

		$query .= "FROM `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
				JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`  
				JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID` ";

    $query .= (($aShowHidden === true) ? 'WHERE 1 ' : 'WHERE `t2`.`display` = 1 ');
	
		if(!is_null($aTitle) && !empty($aTitle)) { 
		    $query .= " AND `t2`.`title` LIKE '%{$aTitle}%' ";
		}
	
		if(!is_null($aTitleOrig) && !empty($aTitleOrig)) { 
		    $query .= " AND `t2`.`title_orig` LIKE '%{$aTitleOrig}%' ";
		}
		
		if(!is_null($aDirector) && !empty($aDirector)) { 
		    $query .= " AND `t2`.`director` LIKE '%{$aDirector}%' ";
		}
		
		if(!is_null($aActor) && !empty($aActor)) { 
		    $query .= " AND `t2`.`actor` LIKE '%{$aActor}%' ";
		}
		
		if(!is_null($aShopno) && !empty($aShopno)) {
		    $query .= " AND `t2`.`shopno` = '{$aShopno}' ";
		}
		
		if(!is_null($aCategory) && !empty($aCategory) && $aCategory != '---') {
		    $query .= " AND `t1`.`genre_category_ID` = '{$aCategory}' ";
		}
		
		if(!$aCount) {
			$query .= "ORDER BY `t2`.`created`, `item_title` ASC ";
		}
		
		echo '<!-- '."\n{$query}\n".'-->';
		
		return $query;
	}
	
	static function getEshopItemsBySearchCount($aTitle, $aTitleOrig, $aDirector, $aActor, $aShopno, $aCategory, $aShowHidden = false)
	{
		global $wpdb;
		
		$query = self::_get_query_for_eshopitems_search(true, $aTitle, $aTitleOrig, $aDirector, $aActor, $aShopno, $aCategory, $aShowHidden);
		$col = $wpdb->get_results($query, ARRAY_A);
		
		if(!is_array($col)) {
			return 0;
		}
		
		return (@count($col) == 1) ? (int) $col[0]['items_count'] : 0;
	}
	
	static function getEshopItemsBySearch($aTitle, $aTitleOrig, $aDirector, $aActor, $aShopno, $aCategory, $aPager = null, $aShowHidden = false)
	{
		global $wpdb;
		
		$ret = array();
		$query = self::_get_query_for_eshopitems_search(false, $aTitle, $aTitleOrig, $aDirector, $aActor, $aShopno, $aCategory, $aShowHidden);
		
		if(!is_null($aPager)) {
			$limit_from = (is_null($aPager)) ? 0 : $aPager->mLimitItemsFrom;
			$limit_to = /*(is_null($aPager)) ? */OD_ESHOP_PAGER_ITEMS_LIMIT/*: $aPager->mLimitItemsTo*/;
			$query .= "LIMIT {$limit_from}, {$limit_to}";
		}
		
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		if(is_array($rows)) {
			foreach($rows as $row) {
				$ret[$row['item_ID']] = odWpEshopPluginDbModel::_process_dbitem($row);
			}
		}
		
		return $ret;
	}
	
	// ========================================================================
	// STATISTICS
	
	static function getStatsForLastDays($aDaysCount = 20)
	{
		global $wpdb;
		
		$query = "
				SELECT 
					count( `t1`.`ID` ) AS `orders_count` , 
					`t1`.`finished_when` AS `order_date` ,
					SUM( (SELECT SUM( `t7`.`prize` ) FROM `{$wpdb->prefix}eshop_orders_items` AS `t5` JOIN `{$wpdb->prefix}eshop_items` AS `t6` ON `t6`.`ID` = `t5`.`eshop_item_ID` JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t7` ON `t7`.`ID` = `t6`.`prize_category_ID` WHERE `t5`.`order_ID` = `t1`.`ID` ) ) AS `order_total_prize` 
				FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
				LEFT JOIN `{$wpdb->prefix}eshop_users` AS `t2` ON `t2`.`wp_user_ID` = `t1`.`user_ID` 
				WHERE 
						`t1`.`finished` = 1
				GROUP BY `finished_when`  
				ORDER BY `finished_when` ASC ";
		return $wpdb->get_results($query, ARRAY_A);
	}
	
	static function getStatsByEshopItems($aLimit = 20)
	{
		global $wpdb;
		
		$query = "
				SELECT 
						`t1`.`eshop_item_ID` AS `item_ID` ,
						`t3`.`title` AS `item_title` , 
						`t3`.`title_orig` AS `item_title_orig` , 
						`t4`.`prize` AS `item_prize` , 
						SUM( `t1`.`count` ) AS `item_sellcount` , 
						( `t4`.`prize` * SUM( `t1`.`count` ) ) AS `item_sell` 
				FROM 
						`wp_eshop_orders_items` AS `t1` 
				LEFT JOIN `wp_eshop_orders` AS `t2` ON `t2`.`ID` = `t1`.`order_ID` 
				LEFT JOIN `wp_eshop_items` AS `t3` ON `t3`.`ID` = `t1`.`eshop_item_ID` 
				LEFT JOIN `wp_eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID` 
				WHERE 
						`t2`.`ID` = `t1`.`order_ID` AND 
						`t2`.`finished` = 1
				GROUP BY `t1`.`eshop_item_ID` 
				ORDER BY 
						`item_sellcount` DESC, 
						`item_sell` DESC 
				LIMIT 0 , {$aLimit} ";
		return $wpdb->get_results($query, ARRAY_A);
	}
	
	static function getStatsByEshopUsers($aLimit = 20)
	{
		global $wpdb;
		
		$query = "
				SELECT 
						`t1`.`user_ID` AS `wp_user_ID`, 
						`t1`.`ID` AS `order_ID` , 
						(SELECT SUM( `t7`.`prize` ) FROM `{$wpdb->prefix}eshop_orders_items` AS `t5` JOIN `{$wpdb->prefix}eshop_items` AS `t6` ON `t6`.`ID` = `t5`.`eshop_item_ID` JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t7` ON `t7`.`ID` = `t6`.`prize_category_ID` WHERE `t5`.`order_ID` = `t1`.`ID` ) AS `order_prize` 
				FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
				WHERE `t1`.`finished` = 1 ";
		$rows = $wpdb->get_results($query, ARRAY_A);
		
		$ret = array();
		$userid = null;
		$i = 1;
		
		foreach($rows as $row) {
			$userid = (int) $row['wp_user_ID'];
			
			if(isset($ret[$userid])) {
				$ret[$userid]['orders_count'] = $ret[$userid]['orders_count'] + 1;
				$ret[$userid]['orders_prize'] = $ret[$userid]['orders_prize'] + (int) $row['order_prize'];
			}
			else {
				$wpuser = new WP_User($userid);
				$ret[$userid]['user_fullname'] = $wpuser->last_name . ', ' . $wpuser->first_name;
				$ret[$userid]['orders_count'] = 1;
				$ret[$userid]['orders_prize'] = (int) $row['order_prize'];
			}
			
			if($i++ == $aLimit) {
				return $ret;
			}
		}
		
		return $ret;
	}
	
}
